package dao;

import entity.Carts;
import entity.Product;
import entity.Users;
import utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 购物车的数据库操作类
 */
public class CartsDAO {
    /**
     * 添加商品到购物车
     * @param pid
     * @param uid
     * @param count
     * @return
     */
    public Integer addCartsById( int pid, int uid , int count) {
        int result = 0;
        String sql = "INSERT INTO CARTS( USERSID, PRODUCTID, CARTSCOUNT) VALUES (?,?,?)";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1,uid);
            pst.setInt(2,pid);
            pst.setInt(3,count);
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 通过商品id和用户id查询购物车
     * @param pid
     * @param uid
     * @return
     */
    public Carts queryCartsByProductAndUser( int pid, int uid ) {
        Carts carts = null;
        String sql = "SELECT * FROM CARTS WHERE USERSID=? AND PRODUCTID=?";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1,uid);
            pst.setInt(2,pid);
            ResultSet rs = pst.executeQuery();
            if ( rs.next() ) {
                carts = new Carts();
                carts.setId(rs.getInt(1));
                carts.setCount(rs.getInt(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return carts;
    }

    /**
     * 查询购物车中的所有商品
     * @return
     */
    public List<Carts> queryAllCarts() {
        List<Carts> cartsList = null;
        String sql = "select * FROM VIEW_CARTS where USERSID=1";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;

        try {
            pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            cartsList = new ArrayList<>();
            while( rs.next() ) {
                Carts cart = new Carts();
                cart.setId(rs.getInt(1));
                cart.setCount(rs.getInt(7));

                Product pro = new Product();
                pro.setId(rs.getInt(4));
                pro.setName(rs.getString(5));
                pro.setPrice(rs.getDouble(6));

                Users user = new Users();
                user.setId(rs.getInt(2));
                user.setName(rs.getString(3));

                cart.setUser(user);
                cart.setProduct(pro);

                cartsList.add(cart);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return cartsList;
    }

    /**
     * 修改商品的数量
     * @param count
     * @param cartsid
     * @return
     */
    public Integer modifyCartsCount( int count, int cartsid ) {
        int result = 0;
        String sql = "UPDATE CARTS SET CARTSCOUNT=CARTSCOUNT+? WHERE CARTSID=?";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, count);
            pst.setInt(2,cartsid);
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 通过id删除商品
     * @param id
     * @return
     */
    public Integer deleteCartsById( int id ) {
        int result = 0;
        String sql = "DELETE FROM CARTS WHERE CARTSID=?";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1,id);
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}
